﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.ComponentModel;

namespace qna.Business {
	[DataObject]
	abstract public class BusinessObject {
		protected static string connectionString = ConfigurationManager.ConnectionStrings["dbString"].ConnectionString;

		protected abstract string TableName { get; }
		protected virtual string PrimaryKey { get { return "Id"; } }

		/// <returns>A new instance of this business object.</returns>
		protected abstract BusinessObject New();

		/// <summary>
		/// Gets the attributes of this business object.
		/// </summary>
		public abstract IDictionary<string, object> Attributes { get; }

		/// <summary>
		/// Reads all the attributes from the reader into this object.
		/// </summary>
		/// <param name="dr">The SQL data reader.</param>
		public abstract void ReadAttributes(SqlDataReader dr);

		/// <summary>
		/// Gets everything from the database
		/// </summary>
		/// <returns></returns>
		[DataObjectMethod(DataObjectMethodType.Select)]
		public virtual IList<BusinessObject> GetAll(int limit = 0) {
			string query = "SELECT ";
			if (limit != 0)
				query += " TOP " + limit;
			query += " * FROM " + TableName;
			return QueryAll(query);
		}

		public virtual IList<BusinessObject> GetAllById(int[] ids, int limit = 0) {
			string query = "SELECT ";
			if (limit != 0)
				query += " TOP " + limit;
			query += " * FROM " + TableName;
			query += " WHERE " + PrimaryKey + " in (";
			foreach (int id in ids) {
				query += id + ", ";
			}
			query = query.Substring(0, query.Length - 2);
			query += ")";
			return QueryAll(query);
		}

		[DataObjectMethod(DataObjectMethodType.Select)]
		public virtual BusinessObject GetById(int id) {
			BusinessObject result = null;
			string sql = "SELECT * FROM " + TableName + " WHERE id = @id";
			IDictionary<string, object> param = new Dictionary<string, object>();
			param["@id"] = id;
			ExecuteReader(sql, param, delegate(SqlDataReader dr) {
				if (dr.Read()) {
					result = New();
					result.ReadAttributes(dr);
				}
				return dr;
			});
			return result;
		}

		/// <summary>
		/// Inserts the current object.
		/// </summary>
		/// <param name="attrs">The attributes to insert.</param>
		/// <param name="ignorePk">Whether to exclude the primary key in the insertion.</param>
		/// <returns>Primary key of the new row. 0 if it failed.</returns>
		public virtual int Insert(IDictionary<string, object> attrs, bool ignorePk = true) {
			IDictionary<string, object> parameters = new Dictionary<string, object>();
			if (ignorePk) {
				parameters["@" + PrimaryKey] = attrs[PrimaryKey];
				attrs.Remove(PrimaryKey);
			}

			//prepare sql
			string sql = "INSERT INTO " + TableName + " (";
			foreach (KeyValuePair<string, object> kvp in attrs) {
				sql += kvp.Key + ", ";
				parameters["@" + kvp.Key] = kvp.Value;
			}
			sql = sql.Substring(0, sql.Length - 2);
			sql += ") VALUES (";
			foreach (KeyValuePair<string, object> kvp in attrs) {
				sql += "@" + kvp.Key + ", ";
			}
			sql = sql.Substring(0, sql.Length - 2);
			sql += "); SELECT @@IDENTITY";

			//execute
			int newId = 0;
			ExecuteReader(sql, parameters, delegate(SqlDataReader dr) {
				//read primary key
				if (dr.Read() && dr[0] != DBNull.Value) {
					newId = Convert.ToInt32(dr[0]);
					this.GetType().GetProperty(PrimaryKey).SetValue(this, newId, null);
				}
				return dr;
			});

			return newId;
		}

		[DataObjectMethod(DataObjectMethodType.Insert, true)]
		public virtual int Insert(bool ignorePk = true) {
			return Insert(Attributes, ignorePk);
		}

		[DataObjectMethod(DataObjectMethodType.Update, true)]
		public virtual bool Update(IDictionary<string, object> attrs, bool ignorePk = true) {
			IDictionary<string, object> parameters = new Dictionary<string, object>();
			if (ignorePk) {
				parameters["@" + PrimaryKey] = attrs[PrimaryKey];
				attrs.Remove(PrimaryKey);
			}
			string sql = "UPDATE " + TableName + " SET ";
			foreach (KeyValuePair<string, object> kvp in attrs) {
				sql += kvp.Key + " = @" + kvp.Key + ", ";
				parameters["@" + kvp.Key] = kvp.Value;
			}
			sql = sql.Substring(0, sql.Length - 2);
			sql += " WHERE " + PrimaryKey + " = @" + PrimaryKey;

			return ExecuteNonQuery(sql, parameters) > 0;
		}

		[DataObjectMethod(DataObjectMethodType.Update, true)]
		public virtual bool Update(bool ignorePk = true) {
			return Update(Attributes, ignorePk);
		}

		[DataObjectMethod(DataObjectMethodType.Delete, true)]
		public bool Delete() {
			string sql = "DELETE FROM " + TableName + " WHERE " + PrimaryKey + " = @" + PrimaryKey;
			IDictionary<string, object> param = new Dictionary<string, object>();
			param["@" + PrimaryKey] = Attributes[PrimaryKey];
			return ExecuteNonQuery(sql, param) > 0;
		}

		/// <summary>
		/// Runs an SQL query that returns a list of results.
		/// </summary>
		/// <param name="sql">SQL to execute.</param>
		/// <param name="parameters">Parameters to bind to the query.</param>
		/// <returns>List of results.</returns>
		protected IList<BusinessObject> QueryAll(string sql, IDictionary<string, object> parameters = null) {
			IList<BusinessObject> results = new List<BusinessObject>();
			Func<SqlDataReader, SqlDataReader> fn = delegate(SqlDataReader dr) {
				while (dr.Read()) {
					BusinessObject obj = New();
					obj.ReadAttributes(dr);
					results.Add(obj);
				}
				return dr;
			};

			if (parameters == null) ExecuteReader(sql, fn);
			else ExecuteReader(sql, parameters, fn);

			return results;
		}

		/// <summary>
		/// Performs a standard SQL read and passes the reader into the callback.
		/// </summary>
		/// <param name="sql">SQL to execute.</param>
		/// <param name="parameters">Parameters to bind to the query.</param>
		/// <param name="callback">Callback that accepts and returns the reader.</param>
		protected void ExecuteReader(string sql, IDictionary<string, object> parameters, Func<SqlDataReader, SqlDataReader> callback) {
			SqlConnection conn = null;
			try {
				conn = new SqlConnection();
				conn.ConnectionString = connectionString;
				conn.Open();
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = sql;
				foreach (KeyValuePair<string, object> kvp in parameters) {
					cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
				}
				SqlDataReader dr = cmd.ExecuteReader();
				callback(dr).Close();
			} catch (SqlException e) {
				HandleSqlException(e);
			} finally {
				conn.Close();
			}
		}

		protected void ExecuteReader(string sql, Func<SqlDataReader, SqlDataReader> callback) {
			ExecuteReader(sql, new Dictionary<string, object>(), callback);
		}

		/// <summary>
		/// Performs a standard non-query.
		/// </summary>
		/// <param name="sql">SQL to execute.</param>
		/// <param name="parameters">Parameters to bind to the query.</param>
		/// <returns></returns>
		protected int ExecuteNonQuery(string sql, IDictionary<string, object> parameters) {
			SqlConnection conn = null;
			int result = 0;
			try {
				conn = new SqlConnection();
				conn.ConnectionString = connectionString;
				conn.Open();
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = sql;
				foreach (KeyValuePair<string, object> kvp in parameters) {
					cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
				}
				result = cmd.ExecuteNonQuery();
			} catch (SqlException e) {
				HandleSqlException(e);
			} finally {
				conn.Close();
			}
			return result;
		}

		protected int ExecuteNonQuery(string sql) {
			return ExecuteNonQuery(sql, new Dictionary<string, object>());
		}

		/// <summary>
		/// Common place too handle SQL exceptions.
		/// </summary>
		/// <param name="e">The exception.</param>
		protected virtual void HandleSqlException(SqlException e) {
			Debug.Write(e);
			throw e;
		}

		/// <summary>
		/// Performs a standard Scalar.
		/// </summary>
		/// <param name="sql">SQL to execute.</param>
		/// <param name="parameters">Parameters to bind to the query.</param>
		/// <returns></returns>
		protected int ExecuteScalar(string sql, IDictionary<string, object> parameters) {
			SqlConnection conn = null;
			int result = 0;
			try {
				conn = new SqlConnection();
				conn.ConnectionString = connectionString;
				conn.Open();
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = sql;
				foreach (KeyValuePair<string, object> kvp in parameters) {
					cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
				}
				result = (int)cmd.ExecuteScalar();
			} catch (SqlException e) {
				HandleSqlException(e);
			} finally {
				conn.Close();
			}
			return result;
		}

		public string GetUserName(int id) {
			string username = "";
			string sql = "SELECT name FROM [User] WHERE id = (SELECT user_id FROM " + TableName + " WHERE id=" + id + ")";
			IDictionary<string, object> param = new Dictionary<string, object>();
			//param["@id"] = id;
			ExecuteReader(sql, param, delegate(SqlDataReader dr) {
				if (dr.Read()) {
					username = (string)dr["name"];
				}
				return dr;
			});
			return username;
		}

        public virtual string UserName(string uname)
        {
            string username = "";
            string sql = "SELECT name FROM [User] WHERE username=@username";
            IDictionary<string, object> param = new Dictionary<string, object>();
            param.Add("@username", uname);
            ExecuteReader(sql, param, delegate(SqlDataReader dr)
            {
                if (dr.Read())
                {
                    username = (string)dr["name"];
                }
                return dr;
            });
            Debug.Write(username);
            return username;
        }
	}
}